/**
 * Example.java
 * Copyright (C) 2009-2010 TMate Software Ltd
 * 
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; version 2 of the License.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * For information on how to redistribute this software under
 * the terms of a license other than GNU General Public License
 * contact TMate Software at support@sqljet.com
 */

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Set;

import org.tmatesoft.sqljet.core.SqlJetException;
import org.tmatesoft.sqljet.core.SqlJetTransactionMode;
import org.tmatesoft.sqljet.core.schema.ISqlJetIndexDef;
import org.tmatesoft.sqljet.core.schema.ISqlJetTableDef;
import org.tmatesoft.sqljet.core.table.ISqlJetCursor;
import org.tmatesoft.sqljet.core.table.ISqlJetTable;
import org.tmatesoft.sqljet.core.table.ISqlJetTransaction;
import org.tmatesoft.sqljet.core.table.SqlJetDb;

/**
 * @author TMate Software Ltd.
 * @author Sergey Scherbina (sergey.scherbina@gmail.com)
 * 
 */
public class Tutorial {

	private static final String DB_NAME = "db.sqlite";
	private static final String TABLE_NAME = "employees";

	private static final String FIRST_NAME_FIELD = "first_name";
	private static final String SECOND_NAME_FIELD = "second_name";
	private static final String DOB_FIELD = "date_of_birth";
	private static final String FULL_NAME_INDEX = "full_name_index";
	private static final String DOB_INDEX = "dob_index";

	public static void main(String[] args) throws SqlJetException {
		File dbFile = new File(DB_NAME);
		dbFile.delete();

		SqlJetDb db = SqlJetDb.open(dbFile, true);

		System.out.println();
		System.out.println(">Database schema objects:");
		System.out.println();
		System.out.println(db.getSchema());
		System.out.println(db.getOptions());

		// insert rows:
		db.beginTransaction(SqlJetTransactionMode.WRITE);
		try {
			ISqlJetTable table = db.getTable("BOOK");
			table.insert();
			
		} finally {
			db.commit();
		}

		ISqlJetTable table = db.getTable(TABLE_NAME);

		// getting all rows in table, sorted by PK.
		System.out.println();
		System.out.println(">All employees in order defined by PK ("
				+ table.getPrimaryKeyIndexName() + "):");
		System.out.println();
		db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
		try {
			printRecords(table.order(table.getPrimaryKeyIndexName()));
		} finally {
			db.commit();
		}

		// getting all rows in table, sorted by PK.
		System.out.println();
		System.out.println(">All employees in order defined by " + DOB_INDEX
				+ ", reversed:");
		System.out.println();
		db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
		try {
			printRecords(table.order(table.getPrimaryKeyIndexName()).reverse());
		} finally {
			db.commit();
		}

		// getting all rows in table, sorted by index.
		System.out.println();
		System.out.println(">All employees in order defined by "
				+ FULL_NAME_INDEX + " :");
		System.out.println();

		db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
		try {
			printRecords(table.order(FULL_NAME_INDEX));
		} finally {
			db.commit();
		}

		// getting rows in table with exact indexed field value.
		System.out.println();
		System.out.println(">Alexanders:");
		System.out.println();
		db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
		try {
			printRecords(table.lookup(FULL_NAME_INDEX, "Alexander"));
		} finally {
			db.commit();
		}

		// getting rows in table with indexed field value in certain scope.
		System.out.println();
		System.out.println(">Employees with full name in scope [B, I]:");
		System.out.println();
		db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
		try {
			printRecords(table.scope(FULL_NAME_INDEX, new Object[] { "B" },
					new Object[] { "I" }));
		} finally {
			db.commit();
		}

		Calendar calendar = Calendar.getInstance();
		calendar.setTime(new Date(System.currentTimeMillis()));
		calendar.add(Calendar.YEAR, -30);

		System.out.println();
		System.out
				.println(">Deleting rows of employees older than 30 years old.");
		System.out.println();
		db.beginTransaction(SqlJetTransactionMode.WRITE);
		try {
			ISqlJetCursor deleteCursor = table.scope(DOB_INDEX,
					new Object[] { Long.MIN_VALUE },
					new Object[] { calendar.getTimeInMillis() });
			while (!deleteCursor.eof()) {
				System.out.println("Deleting: " + deleteCursor.getRowId()
						+ " : " + deleteCursor.getString(FIRST_NAME_FIELD)
						+ " " + deleteCursor.getString(SECOND_NAME_FIELD)
						+ " was born on "
						+ formatDate(deleteCursor.getInteger(DOB_FIELD)));
				deleteCursor.delete();
			}
			deleteCursor.close();
		} finally {
			db.commit();
		}

		System.out.println();
		System.out.println(">After deletion in row id order:");
		System.out.println();
		db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
		try {
			printRecords(table.open());
		} finally {
			db.commit();
		}

		db.beginTransaction(SqlJetTransactionMode.WRITE);
		ISqlJetCursor updateCursor = null;
		try {
			table.insert("Smith", "John", 0);
			calendar.setTime(new Date(System.currentTimeMillis()));
			updateCursor = table.open();
			do {
				updateCursor.update(updateCursor.getValue(SECOND_NAME_FIELD),
						updateCursor.getValue(FIRST_NAME_FIELD),
						calendar.getTimeInMillis());
			} while (updateCursor.next());
		} finally {
			updateCursor.close();
			db.commit();
		}

		System.out.println();
		System.out
				.println(">After insertion of a new record and updating dates (by PK):");
		System.out.println();
		db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
		try {
			printRecords(table.order(table.getPrimaryKeyIndexName()));
			System.out.println();
			System.out.println(">Same in order defined by " + FULL_NAME_INDEX
					+ " :");
			System.out.println();
			printRecords(table.order(FULL_NAME_INDEX));
		} finally {
			db.commit();
		}

		System.out.println();
		System.out.println(">Dropping tables and indices:");
		System.out.println();
		db.beginTransaction(SqlJetTransactionMode.WRITE);
		try {
			Set<String> tables = db.getSchema().getTableNames();
			for (String tableName : tables) {
				ISqlJetTableDef tableDef = db.getSchema().getTable(tableName);
				Set<ISqlJetIndexDef> tableIndices = db.getSchema().getIndexes(
						tableDef.getName());

				for (ISqlJetIndexDef indexDef : tableIndices) {

					if (!indexDef.isImplicit()) {
						System.out.println("dropping index: "
								+ indexDef.getName());
						db.dropIndex(indexDef.getName());
					}
				}
				System.out.println("dropping table: " + tableDef.getName());
				db.dropTable(tableDef.getName());
			}
		} finally {
			db.commit();
		}

		db.close();
	}

	private static void printRecords(ISqlJetCursor cursor)
			throws SqlJetException {
		try {
			if (!cursor.eof()) {
				do {
					System.out.println(cursor.getRowId() + " : "
							+ cursor.getString(FIRST_NAME_FIELD) + " "
							+ cursor.getString(SECOND_NAME_FIELD)
							+ " was born on "
							+ formatDate(cursor.getInteger(DOB_FIELD)));
				} while (cursor.next());
			}
		} finally {
			cursor.close();
		}
	}

	private static String formatDate(long time) {
		return SimpleDateFormat.getDateInstance(SimpleDateFormat.MEDIUM)
				.format(new Date(time));
	}

}